﻿/*
ADD EXT PROPERTIES TO ALL TABLES WITH A PK (NON-HEAP)

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/

SET NOCOUNT ON;

--EXECUTE AS USER = 'todocuser';

BEGIN TRAN

BEGIN TRY

	MERGE INTO ExtProps.Properties AS target
	USING
	(
	-- Is enabled property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Enabled',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has been run property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Run',
			'False'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has insert procedure property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Insert Procedure',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has update procedure property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Update Procedure',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has save procedure property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Save Procedure',
			'False'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has delete procedure property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Delete Procedure',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has shadow/audit table property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Shadow Table',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has view property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has View',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has insert trigger property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Insert Trigger',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has update trigger property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Update Trigger',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	UNION ALL
	-- Has delete trigger property
	SELECT	nht.TableSchema,
			nht.TableName,
			NULL,
			N'Todoc: Has Delete Trigger',
			'True'
	FROM [todoc].[svNonHeapTables] nht
	) AS source([object_schema], [object_name], [column_name], [name], [value])
    ON 
	(
	target.[object_schema] = source.[object_schema]
	AND target.[object_name] = source.[object_name]
	AND target.[name] = source.[name]
	)
    WHEN MATCHED THEN 
        UPDATE SET [value] = source.[value]
	WHEN NOT MATCHED THEN	
	    INSERT ([object_schema], [object_name], [column_name], [name], [value])
	    VALUES (source.[object_schema], source.[object_name], source.[column_name], source.[name], source.[value]);

	COMMIT TRAN;

	--REVERT;

END TRY
BEGIN CATCH

	SELECT ERROR_MESSAGE();

	ROLLBACK TRAN;

	--REVERT;

END CATCH
